import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.jsoup.Jsoup;
import org.jsoup.nodes.Document;
import org.jsoup.nodes.Element;
import org.jsoup.select.Elements;
import util.HttpClientUtil;

import java.io.FileOutputStream;
import java.io.PrintStream;
import java.sql.Connection;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

public class Main103Application {
    private static final String url = "http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/2020/";
    private static final Map<String, String> headers = new HashMap<>();


    /**
     * 这里获取 街道和乡镇的只能单独再写一个了
     * @param args
     * @throws Exception
     */
    public static void main(String[] args) throws Exception {


        // 获取池对象
        ComboPooledDataSource dataSource = new ComboPooledDataSource();
        // 配置连接信息
        dataSource.setDriverClass("com.mysql.cj.jdbc.Driver");
        dataSource.setJdbcUrl("jdbc:mysql://localhost:3308/my-info?serverTimezone=Asia/Shanghai");
        dataSource.setUser("root");
        dataSource.setPassword("123456");
        //设置初始的连接数
        dataSource.setInitialPoolSize(10);
        //获取连接
        Connection connection = dataSource.getConnection();


        // Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3308/my-info?serverTimezone=Asia/Shanghai", "root", "123456");


        QueryRunner queryRunner = new QueryRunner(dataSource);
        /**
         *
         * "SELECT * FROM `admin-region` WHERE  `REGION_TYPE` = '103'"
         * 如果断号了，就顺着最后获取的记录 筛选parentId就可以了
         * "SELECT * FROM `admin-region` WHERE  `REGION_TYPE` = '103' AND `PARENT_ID` > '最后写入的`PARENT_ID`'"
         */
        List<Map<String, Object>> res = queryRunner.query("SELECT * FROM `admin-region` WHERE  `REGION_TYPE` = '103'", new MapListHandler());
        final List<Map<String, String>> townList = new LinkedList<>();

        System.setOut(new PrintStream(new FileOutputStream("C:\\Users\\Administrator\\Desktop\\full.sql")));
        res.forEach(map -> {
            if (null == map.get("LINK")) return;
            // System.out.println(map);
            try {
                Thread.sleep(1000);
            } catch (InterruptedException e) {
                e.printStackTrace();
            }
            getTownList(map, townList);
        });


    }

    public static void getTownList(Map<String, Object> county, List<Map<String, String>> townList) {
        headers.put("Accept", "text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.9");
        headers.put("Accept-Encoding", "gzip, deflate");
        headers.put("Accept-Language", "zh-CN,zh;q=0.9");
        headers.put("Connection", "keep-alive");
        headers.put("Cookie", "SF_cookie_1=72991020; wzws_cid=0a3437ee54d480f4e9d7a803ce40ea209062fb89e676041702640cf8b68a8e6f100515f77a916d43eccbf9bf4ca4373ef6c83e6cddf6596b0c4c99b50dc6442d6370481133143c2de50c8bf386084682");
        headers.put("Host", "www.stats.gov.cn");
        String link = county.get("LINK").toString();
        headers.put("Referer",  link);
        headers.put("Upgrade-Insecure-Requests",  "1");
        headers.put("User-Agent", "Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/86.0.4240.198 Safari/537.36");

        String htmlSourceCode = HttpClientUtil.executeGetRequest(link, headers);
        // System.out.println(htmlSourceCode);
        Document document = Jsoup.parse(htmlSourceCode);
        Elements towntr = document.getElementsByClass("towntr");

        towntr.forEach(element -> {
            Elements tdTags = element.getElementsByTag("td");
            String tdTag1 = tdTags.get(0).html();
            if (tdTag1.contains("href")) {
                Elements aTags = element.getElementsByTag("a");
                Element aTag1 = aTags.get(0);
                Element aTag2 = aTags.get(1);
                String href = aTag1.attr("href");
                String townCode = aTag1.text();
                String townName = aTag2.text();

                Map<String, String> town = new HashMap<>();
                town.put("townName", townName);
                town.put("townCode", townCode);
                //  MainApplication.url
                /**
                 *
                 * http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/2020/13/29/130229401.html
                 * http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/2020/13/02/29/130229104.html
                 */
                String code = href.split("/")[1];
                String code1 = code.substring(0,2) + "/";
                String code2 = code.substring(2,4) + "/";

                town.put("link",  MainApplication.url + code1 + code2 + href);
                townList.add(town);

                //  // INSERT INTO `my-info`.`admin-region` (`ID`, `NAME`, `REGION_TYPE`, `PARENT_ID`, `LICENSE_SHORT`, `LINK`, `GEN_TIME`) VALUES
                System.out.printf("(%s, '%s', '%s', NULL, NULL, '%s', NOW()),\n", townCode, townName, county.get("ID"), town.get("link"));
            } else  {

                String townName = tdTags.get(1).text();
                Map<String, String> town = new HashMap<>();
                town.put("townName", townName);
                town.put("townCode", tdTag1);
                town.put("link", null);
                townList.add(town);
                // INSERT INTO `my-info`.`admin-region` (`ID`, `NAME`, `REGION_TYPE`, `PARENT_ID`, `LICENSE_SHORT`, `LINK`, `GEN_TIME`) VALUES
                System.out.printf("(%s, '%s', '%s', NULL, NULL, NULL, NOW()),\n", tdTag1, townName, county.get("ID"));
            }
        });
    }
}
